using System;
using System.Collections.Generic;
using System.Web;
using System.Data.Common;
using MySql.Data.MySqlClient;
using System.Data;
using System.Configuration;
using System.Text;
using System.Data.SqlClient;
using ResourceBlender_Express.Properties;
using System.Collections;
using System.Data.SQLite;
using System.Globalization;

namespace ResourceBlender_Express {
    /// <summary>
    /// An abstract database connection which can connect to different server types
    /// </summary>
    public class Database {
        public enum DatabaseType {
            [EnumBinder.EnumDescription("MySQL")]
            MySQL,

            [EnumBinder.EnumDescription("MS SQL Server")]
            SQLServer,

            SQLite
        }

        private DbConnection conn;
        private DatabaseType type;
        private String tablePrefix;

        /// <summary>
        /// Gets or sets the type of this database instance.
        /// </summary>
        /// <value>The type of the instance.</value>
        public DatabaseType InstanceType {
            get { return type; }
            set { type = value; }
        }

        /// <summary>
        /// Gets a value indicating whether the connection is open.
        /// </summary>
        /// <value><c>true</c> if connected, otherwise <c>false</c>.</value>
        public bool Connected {
            get {
                if(conn != null && conn.State == ConnectionState.Open)
                    return true;
                else
                    return false;
            }
        }

        public string TablePrefix {
            get { return tablePrefix; }
        }

        /// <summary>
        /// Initializes a new <see cref="Database"/>.
        /// </summary>
        public Database() {
	        this.type = (DatabaseType)Enum.Parse(typeof(DatabaseType), Settings.Default["DBMode"].ToString());
	        this.tablePrefix = Settings.Default["DBTablePrefix"].ToString();
	        switch(this.type) {
	            case DatabaseType.MySQL:
	                conn = new MySqlConnection(string.Format(Constants.MySQLConnecionString, Settings.Default["DBHost"], Settings.Default["DBPort"], Settings.Default["DBDatabase"], Settings.Default["DBUser"], Settings.Default["DBPassword"]));
	                break;
	            case DatabaseType.SQLServer:
	                conn = new SqlConnection(string.Format(Constants.SQL2008ConnectionString, Settings.Default["DBHost"], Settings.Default["DBPort"], Settings.Default["DBDatabase"], Settings.Default["DBUser"], Settings.Default["DBPassword"]));
	                break;
                case DatabaseType.SQLite:
                    conn = new SQLiteConnection(string.Format(Constants.SQLiteConnectionString, Settings.Default["DBPath"]));
                    break;
	        }
		}

        /// <summary>
        /// Opens a connection.
        /// </summary>
        /// <param name="type">The server type.</param>
        /// <param name="connectionString">The connection string.</param>
        public Database(DatabaseType type, string connectionString, string tablePrefix) {
            this.type = type;
            this.tablePrefix = tablePrefix;
            switch(type) {
                case DatabaseType.MySQL:
                    conn = new MySqlConnection(connectionString);
                    break;
                case DatabaseType.SQLServer:
                    conn = new SqlConnection(connectionString);
                    break;
                case DatabaseType.SQLite:
                    conn = new SQLiteConnection(connectionString);
                    break;
            }
            conn.Open();
        }

        /// <summary>
        /// Opens the connection
        /// </summary>
        public void OpenConnection() {
            if(conn.State != ConnectionState.Open) {
                conn.Open();
            }
        }

        /// <summary>
        /// Opens a connection.
        /// </summary>
        /// <param name="type">The server type.</param>
        /// <param name="connectionString">The connection string.</param>
        public void OpenConnection(DatabaseType type, string connectionString) {
            this.type = type;
            switch(type) {
                case DatabaseType.MySQL:
                    conn = new MySqlConnection(connectionString);
                    break;
                case DatabaseType.SQLServer:
                    conn = new SqlConnection(connectionString);
                    break;
                case DatabaseType.SQLite:
                    conn = new SQLiteConnection(connectionString);
                    break;
            }
            conn.Open();
        }

        /// <summary>
        /// Closes the connection.
        /// </summary>
        public void CloseConnection() {
            conn.Close();
        }

        /// <summary>
        /// Creates a database command.
        /// </summary>
        /// <returns></returns>
        public Command CreateCommand() {
            return new Command(type, conn.CreateCommand());
        }

        /// <summary>
        /// Begins a transaction.
        /// </summary>
        /// <returns></returns>
        public DbTransaction BeginTransaction() {
            return conn.BeginTransaction();
        }

        /// <summary>
        /// Creates the tables needed by ResourceBlender.
        /// </summary>
        /// <param name="applications"><c>true</c> if the applications table should be created.</param>
        /// <param name="elements"><c>true</c> if the elements table should be created.</param>
        /// <param name="resources"><c>true</c> if the resources table should be created.</param>
        /// <param name="resourceComments"><c>true</c> if the resource comments table should be created.</param>
        /// <param name="users"><c>true</c> if the users table should be created.</param>
        /// <param name="templates"><c>true</c> if the templates table should be created.</param>
        public void CreateTables(bool applications, bool elements, bool resources, bool resourceComments, bool users, bool templates) {
            if(conn == null) {
                throw new InvalidOperationException("Connection not initialized");
            } else {
                if(conn.State != ConnectionState.Open) {
                    OpenConnection();
                }
                using(Command cmd = CreateCommand()) {
                    StringBuilder sqlBuffer = new StringBuilder();
                    switch(type) {
                        case DatabaseType.MySQL:
                            if(applications) {
                                sqlBuffer.Append(string.Format(@"CREATE TABLE `{0}applications` (
                                          `id` varchar(10) NOT NULL default '',
                                          `displayname` varchar(255) NOT NULL,
                                          `name` varchar(255) NOT NULL,
                                          `defaultlanguage` varchar(7) NOT NULL,
                                          `languages` text NOT NULL,
                                          PRIMARY KEY  (`id`)
                                        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;", TablePrefix));
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                                sqlBuffer.Append(string.Format(@"CREATE TABLE `{0}bundles` (
                                          `id` varchar(10) NOT NULL,
                                          `name` varchar(255) NOT NULL,
                                          `application` varchar(10) NOT NULL,
                                          PRIMARY KEY  (`id`),
                                          UNIQUE KEY `unique_bundle` (`name`,`application`)
                                        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;", TablePrefix));
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                            }

                            if(elements) {
                                sqlBuffer.Append(string.Format(@"CREATE TABLE `{0}elements` (
                                          `id` int(11) NOT NULL auto_increment,
                                          `applicationid` varchar(10) NOT NULL,
                                          `bundle` varchar(10) NOT NULL,
                                          `elementname` varchar(255) NOT NULL,
                                          `resourceid` int(10) default NULL,
                                          `comment` text,
                                          PRIMARY KEY  (`id`),
                                          UNIQUE KEY `uniqueelement` (`applicationid`,`elementname`,`bundle`)
                                        ) ENGINE=InnoDB DEFAULT CHARSET=utf8", TablePrefix));
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                            }

                            if(resources) {
                                sqlBuffer.Append(string.Format(@"CREATE TABLE `{0}resources` (
                                          `resourceid` int(10) NOT NULL default '0',
                                          `language` varchar(10) NOT NULL,
                                          `translation` text NOT NULL,
                                          `translatorcomment` text,
                                          PRIMARY KEY  (`resourceid`,`language`)
                                        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;", TablePrefix));
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                            }

                            if(resourceComments) {
                                sqlBuffer.Append(string.Format(@"CREATE TABLE `{0}resourcecomments` (
                                          `resourceid` int(10) NOT NULL,
                                          `comment` text,
                                          PRIMARY KEY  (`resourceid`)
                                        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;", TablePrefix));
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                            }

                            if(users) {
                                sqlBuffer.Append(string.Format(@"CREATE TABLE `{0}users` (
                                          `id` int(255) NOT NULL auto_increment,
                                          `username` varchar(255) NOT NULL,
                                          `password` varchar(255) NOT NULL,
                                          `email` text,
                                          `permissions` int(11) NOT NULL default '0',
                                          `allowedprojects` text,
                                          PRIMARY KEY  (`id`),
                                          UNIQUE KEY `unique_username` (`username`)
                                        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;", TablePrefix));
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                            }

                            if(templates) {
                                sqlBuffer.Append(string.Format(@"CREATE TABLE `{0}templates` (
                                              `id` int(5) NOT NULL auto_increment,
                                              `name` varchar(255) NOT NULL,
                                              `filename` varchar(255) NOT NULL,
                                              `filestart` text NOT NULL,
                                              `line` varchar(255) NOT NULL,
                                              `fileend` text NOT NULL,
                                              PRIMARY KEY  (`id`),
                                              UNIQUE KEY `unique_name` (`name`)
                                            ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;", TablePrefix));
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                                sqlBuffer.Append(string.Format(@"INSERT INTO `{0}templates` (`name`, `filename`, `filestart`, `line`, `fileend`) VALUES ", TablePrefix))
                                         .Append(@"('PHP Definitions', '{=LanguageCode}_{=Bundle}.php', '<?php', 'define(""{=Element}"",""{=Translation}"");', '?>'),
                                                   ('PHP Array', '{=Bundle}_{=LanguageCode}.php', '<?php\r\n${=Bundle} = array(', '""{=Element}"" => ""{=Translation}"",', '); ?>');");
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                            }
                            break;
                        case DatabaseType.SQLServer:
                            if(applications) {
                                sqlBuffer.Append(string.Format(@"CREATE TABLE [dbo].[{0}applications](
	                                        [id] [varchar](10) NOT NULL,
	                                        [displayname] [nvarchar](max) NOT NULL,
	                                        [name] [nvarchar](max) NOT NULL,
	                                        [defaultlanguage] [varchar](7) NOT NULL,
	                                        [languages] [varchar](max) NOT NULL
                                        CONSTRAINT [PK_{0}applications] PRIMARY KEY CLUSTERED ([id] ASC))", TablePrefix));
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                                sqlBuffer.Append(string.Format(@"CREATE TABLE [dbo].[{0}bundles] (
                                            [id] [varchar](10) NOT NULL,
	                                        [name] [nvarchar](200) NOT NULL,
                                            [application] [varchar](10) NOT NULL,
                                        CONSTRAINT [PK_{0}bundle] PRIMARY KEY CLUSTERED ([id] ASC),
                                        CONSTRAINT [unique_{0}bundle] UNIQUE NONCLUSTERED ([name] ASC,[application] ASC))", TablePrefix));
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                            }

                            if(elements) {
                                sqlBuffer.Append(string.Format(@"CREATE TABLE [dbo].[{0}elements](
	                                        [id] [int] IDENTITY(1,1) NOT NULL,
	                                        [applicationid] [varchar](10) NOT NULL,
	                                        [bundle] [varchar](10) NOT NULL,
	                                        [elementname] [nvarchar](255) NOT NULL,
	                                        [resourceid] [int] NULL,
	                                        [comment] [nvarchar](max) NULL,
                                        CONSTRAINT [PK_{0}element] PRIMARY KEY CLUSTERED ([id] ASC),
                                        CONSTRAINT [ix_{0}uniqueelement] UNIQUE NONCLUSTERED ([applicationid] ASC,[bundle] ASC,[elementname] ASC))", TablePrefix));
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                            }

                            if(resources) {
                                sqlBuffer.Append(string.Format(@"CREATE TABLE [dbo].[{0}resources](
	                                        [resourceid] [int] NOT NULL,
	                                        [language] [varchar](10) NOT NULL,
	                                        [translation] [nvarchar](max) NOT NULL,
                                            [translatorcomment] nvarchar(max) NULL,
                                        CONSTRAINT [PK_{0}resources] PRIMARY KEY CLUSTERED ([resourceid] ASC, [language] ASC))", TablePrefix));
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                            }

                            if(resourceComments) {
                                sqlBuffer.Append(string.Format(@"CREATE TABLE [dbo].[{0}resourcecomments](
	                                        [resourceid] [int] NOT NULL,
	                                        [comment] [nvarchar](max) NULL,
                                        CONSTRAINT [PK_{0}resourcecomments] PRIMARY KEY CLUSTERED ([resourceid] ASC))", TablePrefix));
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                            }

                            if(users) {
                                sqlBuffer.Append(string.Format(@"CREATE TABLE [dbo].[{0}users](
	                                        [id] [int] IDENTITY(1,1) NOT NULL,
	                                        [username] [nvarchar](100) NOT NULL,
	                                        [password] [nvarchar](100) NOT NULL,
	                                        [email] [nvarchar](100) NULL,
	                                        [permissions] [int] NOT NULL,
	                                        [allowedprojects] [nvarchar](max) NULL,
                                        CONSTRAINT [PK_{0}users] PRIMARY KEY CLUSTERED ([id] ASC),
                                        CONSTRAINT [ix_{0}uniqueusername] UNIQUE NONCLUSTERED ([username] ASC))", TablePrefix));
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                            }

                            if(templates) {
                                sqlBuffer.Append(string.Format(@"CREATE TABLE [dbo].[{0}templates] (
                                              [id] [int] IDENTITY(10,1) NOT NULL,
                                              [name] [nvarchar](100) NOT NULL,
                                              [filename] [nvarchar](max) NOT NULL,
                                              [filestart] [nvarchar](max) NOT NULL,
                                              [line] [nvarchar](max) NOT NULL,
                                              [fileend] [nvarchar](max) NOT NULL,
                                        CONSTRAINT [PK_{0}templates] PRIMARY KEY CLUSTERED ([id] ASC),
                                        CONSTRAINT [ix_{0}templatename] UNIQUE NONCLUSTERED ([name] ASC))", TablePrefix));
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                                sqlBuffer.Append(string.Format(@"INSERT INTO [dbo].[{0}templates] ([name], [filename], [filestart], [line], [fileend]) VALUES ", TablePrefix))
                                         .Append(@"('PHP Definitions', '{=LanguageCode}_{=Bundle}.php', '<?php', 'define(""{=Element}"",""{=Translation}"");', '?>'),
                                                   ('PHP Array', '{=Bundle}_{=LanguageCode}.php', '<?php ${=Bundle} = array(', '""{=Element}"" => ""{=Translation}"",', '); ?>');");
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                            }
                            break;
                        case DatabaseType.SQLite:
                            if(applications) {
                                sqlBuffer.Append(string.Format(@"CREATE TABLE {0}applications (
                                          id text(10) PRIMARY KEY NOT NULL default '',
                                          displayname text(255) NOT NULL,
                                          name text(255) NOT NULL,
                                          defaultlanguage text(7) NOT NULL,
                                          languages text NOT NULL );", TablePrefix));
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                                sqlBuffer.Append(string.Format(@"CREATE TABLE {0}bundles (
                                          id text(10) PRIMARY KEY NOT NULL,
                                          name text(255) NOT NULL,
                                          application text(10) NOT NULL,
                                          UNIQUE (name,application) );", TablePrefix));
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                            }

                            if(elements) {
                                sqlBuffer.Append(string.Format(@"CREATE TABLE {0}elements (
                                          id INTEGER PRIMARY KEY AUTOINCREMENT,
                                          applicationid text(10) NOT NULL,
                                          bundle text(10) NOT NULL,
                                          elementname text(255) NOT NULL,
                                          resourceid int(10) default NULL,
                                          comment text,
                                          UNIQUE (applicationid,elementname,bundle) );", TablePrefix));
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                            }

                            if(resources) {
                                sqlBuffer.Append(string.Format(@"CREATE TABLE {0}resources (
                                          resourceid int(10) NOT NULL default '0',
                                          language text(10) NOT NULL,
                                          translation text NOT NULL,
                                          translatorcomment text,
                                          PRIMARY KEY (resourceid,language) );", TablePrefix));
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                            }

                            if(resourceComments) {
                                sqlBuffer.Append(string.Format(@"CREATE TABLE {0}resourcecomments (
                                          resourceid int(10) PRIMARY KEY NOT NULL,
                                          comment text );", TablePrefix));
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                            }

                            if(users) {
                                sqlBuffer.Append(string.Format(@"CREATE TABLE {0}users (
                                          id INTEGER PRIMARY KEY AUTOINCREMENT,
                                          username text(255) NOT NULL UNIQUE,
                                          password text(255) NOT NULL,
                                          email text,
                                          permissions int(11) NOT NULL default '0',
                                          allowedprojects text );", TablePrefix));
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                            }

                            if(templates) {
                                sqlBuffer.Append(string.Format(@"CREATE TABLE {0}templates (
                                          id INTEGER PRIMARY KEY AUTOINCREMENT,
                                          name text(255) NOT NULL UNIQUE,
                                          filename text(255) NOT NULL,
                                          filestart text NOT NULL,
                                          line text(255) NOT NULL,
                                          fileend text NOT NULL );", TablePrefix));
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                                sqlBuffer.Append(string.Format(@"INSERT INTO {0}templates (name, filename, filestart, line, fileend) VALUES ", TablePrefix))
                                         .Append(@"('PHP Definitions', '{=LanguageCode}_{=Bundle}.php', '<?php', 'define(""{=Element}"",""{=Translation}"");', '?>')");
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                                sqlBuffer.Append(string.Format(@"INSERT INTO {0}templates (name, filename, filestart, line, fileend) VALUES ", TablePrefix))
                                         .Append(@"('PHP Array', '{=Bundle}_{=LanguageCode}.php', '<?php ${=Bundle} = array(', '""{=Element}"" => ""{=Translation}"",', '); ?>');");
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                            }
                            break;
                    }
                }
            }
        }

        /// <summary>
        /// Drops all tables created by ResourceBlender.
        /// </summary>
        /// <param name="applications"><c>true</c> if the applications table should be dropped.</param>
        /// <param name="elements"><c>true</c> if the elements table should be dropped.</param>
        /// <param name="resources"><c>true</c> if the resources table should be dropped.</param>
        /// <param name="resourceComments"><c>true</c> if the resource comments table should be dropped.</param>
        /// <param name="users"><c>true</c> if the users table should be dropped.</param>
        public void DropTables(bool applications, bool elements, bool resources, bool resourceComments, bool users, bool templates) {
            if(conn == null) {
                throw new InvalidOperationException("Connection not initialized");
            } else {
                if(conn.State != ConnectionState.Open) {
                    OpenConnection();
                }
                using(Command cmd = CreateCommand()) {
                    if(applications) {
                        cmd.CommandText = "DROP TABLE " + tablePrefix + "applications;";
                        cmd.ExecuteNonQuery();
                        cmd.CommandText = "DROP TABLE " + tablePrefix + "bundles;";
                        cmd.ExecuteNonQuery();
                    }
                    if(elements) {
                        cmd.CommandText = "DROP TABLE " + tablePrefix + "elements";
                        cmd.ExecuteNonQuery();
                    }
                    if(resources) {
                        cmd.CommandText = "DROP TABLE " + tablePrefix + "resources";
                        cmd.ExecuteNonQuery();
                    }
                    if(resourceComments) {
                        cmd.CommandText = "DROP TABLE " + tablePrefix + "resourcecomments";
                        cmd.ExecuteNonQuery();
                    }
                    if(templates) {
                        cmd.CommandText = "DROP TABLE " + tablePrefix + "templates";
                        cmd.ExecuteNonQuery();
                    }
                    if(users) {
                        cmd.CommandText = "DROP TABLE " + tablePrefix + "users";
                        cmd.ExecuteNonQuery();
                    }
                }
            }
        }

        /// <summary>
        /// Generates an IN clause or an empty string if there are no values.
        /// </summary>
        /// <param name="fieldName">Name of the field.</param>
        /// <param name="values">The values to include.</param>
        /// <param name="useAnd">If <c>true</c>, the clause will be prepended by AND.</param>
        /// <returns>An SQL IN clause</returns>
        public string GenerateInClause(string fieldName, ICollection<string> values, bool useAnd) {
            if(values.Count == 0) {
                return string.Empty;
            } else {
                StringBuilder buffer = new StringBuilder(useAnd ? " AND " : " ");
                buffer.Append(fieldName).Append(" IN('").Append(Utils.Implode(values, "', '", false)).Append("')");
                return buffer.ToString();
            }
        }

        /// <summary>
        /// Generates the crosstab part of a query needed to retrieve columns for the specified languages. Does not include leading or trailing commas.
        /// </summary>
        /// <param name="languages">The languages for which to create columns.</param>
        /// <returns>The crosstab part of a query needed to retrieve columns for the specified languages. Does not include leading or trailing commas.</returns>
        public string GetCrosstabLanguageSql(ObservableSortedDictionary<string, CultureInfo> languages) {
            StringBuilder sqlBuffer = new StringBuilder();
            string crossTabFormat = null;
            switch(InstanceType) {
                case Database.DatabaseType.MySQL:
                    crossTabFormat = "MAX(IF(language='{0}',translation,null)) AS '{0}',";
                    break;
                case Database.DatabaseType.SQLServer:
                case Database.DatabaseType.SQLite:
                    crossTabFormat = "MAX(CASE WHEN language = '{0}' THEN translation ELSE NULL END) AS '{0}',";
                    break;
            }
            foreach(KeyValuePair<string, CultureInfo> language in languages) {
                sqlBuffer.AppendFormat(crossTabFormat, language.Key);
            }
            sqlBuffer.Remove(sqlBuffer.Length - 1, 1);
            return sqlBuffer.ToString();
        }

        /// <summary>
        /// Creates a connection string.
        /// </summary>
        /// <param name="type">The server type.</param>
        /// <param name="host">The host.</param>
        /// <param name="port">The port.</param>
        /// <param name="user">The username.</param>
        /// <param name="pass">The password.</param>
        /// <param name="database">The database name.</param>
        /// <returns></returns>
        public static string GetConnectionString(DatabaseType type, string host, int port, string user, string pass, string database) {
            switch(type) {
                case DatabaseType.MySQL:
                    return string.Format(Constants.MySQLConnecionString, host, port, database, user, pass);
                case DatabaseType.SQLServer:
                    return string.Format(Constants.SQL2008ConnectionString, host, port, database, user, pass);
            }
            return string.Empty;
        }

        public static string GetSQLiteConnectioString(string dbPath) {
            return string.Format(Constants.SQLiteConnectionString, dbPath);
        }

        public class DataAdapter {
            private Command cmd;
            private DbDataAdapter adapter;

            public DataAdapter(Command cmd) {
                this.cmd = cmd;
                switch(cmd.DBType) {
                    case DatabaseType.MySQL:
                        this.adapter = new MySqlDataAdapter(cmd.InnerCommand as MySqlCommand);
                        break;
                    case DatabaseType.SQLServer:
                        this.adapter = new SqlDataAdapter(cmd.InnerCommand as SqlCommand);
                        break;
                    case DatabaseType.SQLite:
                        this.adapter = new SQLiteDataAdapter(cmd.InnerCommand as SQLiteCommand);
                        break;
                }
            }

            public void Fill(DataSet ds) {
                adapter.Fill(ds);
            }
            public void Fill(DataTable dt) {
                adapter.Fill(dt);
            }
        }

        public class Command : IDisposable {
            public DbCommand InnerCommand { get; set; }
            public DatabaseType DBType { get; set; }

            public DbParameterCollection Parameters {
                get { return InnerCommand.Parameters; }
            }

            public string CommandText {
                get { return InnerCommand.CommandText; }
                set { InnerCommand.CommandText = value; }
            }

            public Command(DatabaseType type, DbCommand cmd) {
                this.DBType = type;
                this.InnerCommand = cmd;
            }

            public void AddParameterWithValue(string name, object value) {
                switch(DBType) {
                    case DatabaseType.MySQL:
                        (InnerCommand as MySqlCommand).Parameters.AddWithValue(name, value);
                        break;
                    case DatabaseType.SQLServer:
                        (InnerCommand as SqlCommand).Parameters.AddWithValue(name, value);
                        break;
                    case DatabaseType.SQLite:
                        (InnerCommand as SQLiteCommand).Parameters.AddWithValue(name, value);
                        break;
                }
            }

            public DbDataReader ExecuteReader() {
                return InnerCommand.ExecuteReader();
            }
            public DbDataReader ExecuteReader(CommandBehavior behaviour) {
                return InnerCommand.ExecuteReader(behaviour);
            }
            public object ExecuteScalar() {
                return InnerCommand.ExecuteScalar();
            }

            public int ExecuteNonQuery() {
                return InnerCommand.ExecuteNonQuery();
            }

            #region IDisposable Members
            public void Dispose() {
                InnerCommand.Dispose();
            }
            #endregion
        }

        /// <summary>
        /// Loads a list of applications stored by ResourceBlender.
        /// </summary>
        /// <param name="includeBundles"><c>true</c> if a bundle data should also be included.</param>
        /// <returns>
        /// A list of Applications containing all application details
        /// </returns>
        public List<RBApplication> LoadApplications(bool includeBundles) {
            List<RBApplication> applications = new List<RBApplication>();
            try {
                OpenConnection();
                using(Command cmd = CreateCommand()) {
                    cmd.CommandText = string.Format("SELECT * FROM {0}applications ORDER BY displayname", TablePrefix);
                    DbDataReader reader = cmd.ExecuteReader();
                    while(reader.Read()) {
                        applications.Add(new RBApplication(reader));
                    }
                    reader.Close();
                    if(includeBundles) {
                        cmd.CommandText = string.Format("SELECT id, name FROM {0}bundles WHERE application = @app ORDER BY name", TablePrefix);
                        cmd.AddParameterWithValue("@app", "");
                        foreach(RBApplication app in applications) {
                            cmd.Parameters[0].Value = app.ID;
                            reader = cmd.ExecuteReader();
                            while(reader.Read()) {
                                app.Bundles.Add(reader["id"].ToString(), reader["name"].ToString());
                            }
                            reader.Close();
                        }
                    }
                }
            } catch(Exception ex) {
                throw ex;
            } finally {
                try {
                    CloseConnection();
                } catch { }
            }
            return applications;
        }

        /// <summary>
        /// Loads a dictionary containing the languages of all resources in the database, sorted by English Name. Only existing resources are used to populate the dictionary.
        /// Key = LanguageCode, Value = EnglishName
        /// </summary>
        /// <returns>A dictionary containing the CultureInfo of all resources in the database</returns>
        public ObservableSortedDictionary<string, CultureInfo> LoadUniqueLanguages() {
            ObservableSortedDictionary<string, CultureInfo> languages = new ObservableSortedDictionary<string, CultureInfo>(new Comparers.CultureInfoEnglshNameComparer(), new Comparers.StringEqualityComparer());
            try {
                OpenConnection();
                using(Command cmd = CreateCommand()) {
                    cmd.CommandText = string.Format("SELECT DISTINCT language FROM {0}resources", TablePrefix);
                    DbDataReader reader = cmd.ExecuteReader();
                    while(reader.Read()) {
                        CultureInfo info = Utils.GetCultureInfo((reader[0] ?? string.Empty).ToString());
                        if(info != null) {
                            languages.Add(info.Name, info);
                        }
                    }
                    reader.Close();
                }
            } catch(Exception ex) {
                throw ex;
            } finally {
                try {
                    CloseConnection();
                } catch { }
            }
            return languages;
        }
    }
}